﻿namespace SRM.DataObjects
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// Summary description for SRM_Family
    /// </summary>
    [DataObjectAttribute(true)]
    public class SRM_Family
    {

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetFamily(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format(@"SELECT  fm.FamilyMemberKey,
        fm.ApplicantKey,
        fm.MemberName,
        rel.DisplayText AS 'RelationshipDesc',
        fm.BirthDate,
        nat.DisplayText AS 'NationalityDesc',
        fm.FamilyEmployer,
        fm.Occupation,
        fm.EmergencyContact,
        fm.ContactNo,
        fm.Timestamp,
        fm.Surname,
        fm.BirthPlace,
        gen.DisplayText AS 'GenderDesc',
        fm.IdNumber,
        res.DisplayText AS 'ResidencyTypeDesc',
        fm.Gender,
        fm.Relationship,
        fm.Nationality,
        fm.ResidencyType
FROM    SRM_ApplicantFamilyMembers_Tbl fm
LEFT JOIN
        SAP_Code nat 
        ON  nat.CodeTypeId = 'NAT'
        AND nat.Id = fm.Nationality
LEFT JOIN
        SAP_Code rel
        ON  rel.CodeTypeId = 'REL'
        AND rel.Id = fm.Relationship
LEFT JOIN
        SAP_Code gen
        ON  gen.CodeTypeId = 'GEN'
        AND gen.Id = fm.Gender
LEFT JOIN
        SAP_Code res
        ON  res.CodeTypeId = 'RES'
        AND res.Id = fm.ResidencyType
WHERE   fm.ApplicantKey = {0};", applicantKey);

                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        } // public static DataTable GetFamily(string applicantKey)



        [DataObjectMethodAttribute(DataObjectMethodType.Insert, true)]
        public static void InsertFamily(string applicantKey, 
            string memberName, string relationship, string birthDate, string nationality, string familyEmployer, 
            string occupation, string emergencyContact, string contactNo, string timestamp, 
            string surname, string birthPlace, string gender, string idNumber, string residencyType)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "INSERT SRM_ApplicantFamilyMembers_Tbl (ApplicantKey,MemberName,Relationship,BirthDate,Nationality,FamilyEmployer,Occupation,EmergencyContact,ContactNo,Timestamp,Surname,BirthPlace,Gender,IdNumber,ResidencyType) VALUES (@ApplicantKey,@MemberName,@Relationship,@BirthDate,@Nationality,@FamilyEmployer,@Occupation,@EmergencyContact,@ContactNo,@Timestamp,@Surname,@BirthPlace,@Gender,@IdNumber,@ResidencyType);";
                
//@ApplicantKey,
//@MemberName,
//@Relationship,
//@BirthDate,
//@Nationality,
//@FamilyEmployer,
//@Occupation,
                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);
                cmd.Parameters.AddWithValue("@MemberName", Help.GetValueOrDBNull(memberName));
                cmd.Parameters.AddWithValue("@Relationship", Help.GetValueOrDBNull(relationship));
                cmd.Parameters.AddWithValue("@BirthDate", Help.GetValueOrDBNull(birthDate));
                cmd.Parameters.AddWithValue("@Nationality", Help.GetValueOrDBNull(nationality));
                cmd.Parameters.AddWithValue("@FamilyEmployer", Help.GetValueOrDBNull(familyEmployer));
                cmd.Parameters.AddWithValue("@Occupation", Help.GetValueOrDBNull(occupation));

//@EmergencyContact,
//@ContactNo,
//@Timestamp,
                cmd.Parameters.AddWithValue("@EmergencyContact", Help.GetValueOrDBNull(emergencyContact));
                cmd.Parameters.AddWithValue("@ContactNo", Help.GetValueOrDBNull(contactNo));
                cmd.Parameters.AddWithValue("@Timestamp", Help.GetValueOrDBNull(timestamp));
//@Surname,
//@Gender,
//@IdNumber,
//@ResidencyType string surname, string gender, string idNumber, string residencyType)
                cmd.Parameters.AddWithValue("@Surname", Help.GetValueOrDBNull(surname));
                cmd.Parameters.AddWithValue("@BirthPlace", Help.GetValueOrDBNull(birthPlace));
                cmd.Parameters.AddWithValue("@Gender", Help.GetValueOrDBNull(gender));
                cmd.Parameters.AddWithValue("@IdNumber", Help.GetValueOrDBNull(idNumber));
                cmd.Parameters.AddWithValue("@ResidencyType", Help.GetValueOrDBNull(residencyType));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)



        [DataObjectMethodAttribute(DataObjectMethodType.Delete, true)]
        public static void DeleteFamily(string familyMemberKey)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "DELETE FROM SRM_ApplicantFamilyMembers_Tbl WHERE FamilyMemberKey = @FamilyMemberKey;";
                cmd.Parameters.AddWithValue("@FamilyMemberKey", familyMemberKey);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)



        [DataObjectMethodAttribute(DataObjectMethodType.Update, true)]
        public static void UpdateFamily(string familyMemberKey,
            string memberName, string relationship, string birthDate, string nationality, string familyEmployer,
            string occupation, string emergencyContact, string contactNo, string timestamp,
            string surname, string birthPlace, string gender, string idNumber, string residencyType)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"
UPDATE  SRM_ApplicantFamilyMembers_Tbl 
SET     MemberName = @MemberName, 
        Relationship = @Relationship,
        BirthDate = @BirthDate,
        Nationality = @Nationality,
        FamilyEmployer = @FamilyEmployer,
        Occupation = @Occupation,
        EmergencyContact = @EmergencyContact,
        ContactNo = @ContactNo,
        Timestamp = @Timestamp,
        Surname = @Surname,
        BirthPlace = @BirthPlace, 
        Gender = @Gender,
        IdNumber = @IdNumber,
        ResidencyType = @ResidencyType
WHERE   FamilyMemberKey = @FamilyMemberKey;";

                cmd.Parameters.AddWithValue("@FamilyMemberKey", familyMemberKey);
                //@MemberName,
                //@Relationship,
                //@BirthDate,
                //@Nationality,
                //@FamilyEmployer,
                
                cmd.Parameters.AddWithValue("@MemberName", Help.GetValueOrDBNull(memberName));
                cmd.Parameters.AddWithValue("@Relationship", Help.GetValueOrDBNull(relationship));
                cmd.Parameters.AddWithValue("@BirthDate", Help.GetValueOrDBNull(birthDate));
                cmd.Parameters.AddWithValue("@Nationality", Help.GetValueOrDBNull(nationality));
                cmd.Parameters.AddWithValue("@FamilyEmployer", Help.GetValueOrDBNull(familyEmployer));

                //@Occupation,
                //@EmergencyContact,
                //@ContactNo,
                //@Timestamp,
                cmd.Parameters.AddWithValue("@Occupation", Help.GetValueOrDBNull(occupation));
                cmd.Parameters.AddWithValue("@EmergencyContact", Help.GetValueOrDBNull(emergencyContact));
                cmd.Parameters.AddWithValue("@ContactNo", Help.GetValueOrDBNull(contactNo));
                cmd.Parameters.AddWithValue("@Timestamp", Help.GetValueOrDBNull(timestamp));
                //@Surname,
                //@Gender,
                //@IdNumber,
                //@ResidencyType string surname, string gender, string idNumber, string residencyType)
                cmd.Parameters.AddWithValue("@Surname", Help.GetValueOrDBNull(surname));
                cmd.Parameters.AddWithValue("@BirthPlace", Help.GetValueOrDBNull(birthPlace));
                cmd.Parameters.AddWithValue("@Gender", Help.GetValueOrDBNull(gender));
                cmd.Parameters.AddWithValue("@IdNumber", Help.GetValueOrDBNull(idNumber));
                cmd.Parameters.AddWithValue("@ResidencyType", Help.GetValueOrDBNull(residencyType));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void UpdateFamily(string FamilyKey,

    }
}